#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use List::Compare;
use utf8;
use Encode;

# ATTENTION!!!!!!!!!!!!!!!!!!!!!!!!! lorsque le test n'a pas eu lieu, la colonne date_test doit etre vide !!!!!!!!!!!!!!!!!!!!!!! #
# il ne doit pas y avoir de fiche type ou fiche bilan en feuille de début de fichier #

my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
$exp=~s/_/\ /;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;
my %result;

foreach($workbook->worksheets()){
	my $microcebe;
	$worksheet=$_;
	
	### recuperation code_animal ###
    my $micro=$worksheet->get_name();
	my @mic=split('#',$micro);
	$microcebe=$mic[1];
	
	my ($row_min,$row_max) = $worksheet->row_range();
	my ($col_min, $col_max) = $worksheet->col_range();
	my %infos;
	my $naissance;
	my $date='';
	my %date_test;

	for my $row ($row_min .. $row_max){ #parcours lignes
		my $trial;
		my  @value;
		my $tps;
		
		for my $col ($col_min .. $col_max){ #parcours colonnes
			if ($col eq 1 and $row eq 0) { #recup date naissance
				my $cont=$worksheet->get_cell($row,$col);
				$naissance=$cont->value();
			}
			
			if ($row>1){
				if ($col eq 1){ #récupération date_test
					my $cont=$worksheet->get_cell($row,$col);
					if ($cont){
						$date=$cont->value();
					}
				}
				if($date ne ''){					
					if($col eq 5){ #récupération nom trial
						my $cont=$worksheet->get_cell($row,$col);
							$trial=$cont->value();
					}
					
					elsif($col eq 7 and $trial){ #récupération temps
						my $cont=$worksheet->get_cell($row,$col);
						if($cont){
							$tps=$cont->value();
						}
					}
					
					elsif($col eq 9 and $trial and $tps){ #récupération valeur
						my $cont=$worksheet->get_cell($row,$col);
						if($cont){
							push(@value,$tps);
							push(@value,$cont->value());
						}
					}
				}
			}
			
		}
		if($date ne '' and $trial and @value){
			$date_test{$trial}=\@value;
		}
		
		if ($trial and $trial eq 'T4'){
			$infos{'naissance'}=$naissance;
			$infos{$date}={%date_test};
			%date_test=();
			$date='';
		}
	}
	$result{$microcebe}={%infos};
}


#~ my $util;
#~ my $util2;
#~ foreach $util (keys %result) {
	#~ print "\n\n$util : \n";
    #~ foreach $util2 (keys %{$result{$util}}) {
		#~ if($util2 ne 'naissance'){
			#~ print "$util2\n";
			#~ foreach (keys %{$result{$util}{$util2}}){
				#~ print "$_ -> $result{$util}{$util2}{$_}[0], $result{$util}{$util2}{$_}[1]\n";
			#~ }
		#~ }
		#~ else{
			#~ print "naissance: $result{$util}{$util2}\n";
		#~ }
    #~ }
#~ }





# ********************* INSERTION DONNEES DANS LA BASE **************************** #

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});
# execute INSERT query


# ************************************* Création type_test ************************************* #

my @unite_mesure=('T1 temps','T1 nb erreurs','T2 temps','T2 nb erreurs','T3 temps','T3 nb erreurs','T4 temps','T4 nb erreurs');
my @id_type_test;
my $row=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='Barnes bc12' AND unite_mesure=?");

foreach my $elt(@unite_mesure){
	$row->execute($elt);
	while(my @unite=$row->fetchrow_array()){
		push(@id_type_test,$unite[0]);
	}
	$row->finish();
}
if(not @id_type_test){
	foreach my $elt(@unite_mesure){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', 'Barnes bc12', '$elt')"); 
		$row->execute($elt);
		while(my @unite=$row->fetchrow_array()){
			push(@id_type_test,$unite[0]);
		}
		$row->finish();
	}
}

foreach my $code (sort keys %result) { #$code =code_animal
	#recuperation du code_animal et de sa date de naissance =>creation de l'animal
	my $date=$result{$code}{'naissance'};
	my $id_anim;
	my @tab;

	# ************************************* Création animal ************************************* #
	
	### verification de l'existence de l'animal dans la base
	my $temp=$dbh->prepare("SELECT distinct(id_animal,date_naissance) FROM Animal WHERE code_animal=(E'$code')");
	$temp->execute();
	while (my @val=$temp->fetchrow_array()){
		push(@tab,$val[0]);
	}
	$temp->finish();
	
	my $size=@tab;
	if($size eq 0){ #aucun animal n'existe avec ce code
		$dbh->do("INSERT INTO Animal(code_animal,date_naissance) VALUES ('$code','$date')");
		$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code') and date_naissance=(E'$date')");
	}
	elsif($size eq 1){ #un seul animal existe avec ce code, on vérifie que les dates de naissance correspondent
		foreach my $i(@tab){
			my @temp=split(',',$i);
			my $id=$temp[0];
			$id=~s/\(//; #id trouvé
			my $nai=$temp[1];
			$nai=~s/\)//; #date de naissance trouvée
			#~ print "$id; $nai,\n";
			if($nai ne ''){ #si l'animal existant a une date de naissance
				if($nai eq $date){ #on vérifie que les dates coïncident
					$id_anim=$id;
					last;
				}
				else{ #si elles ne coïncident pas, on créé un nouvel animal
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance) VALUES ('$code','$date')");
					$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code') and date_naissance=(E'$date')");
					last;
				}
			}
			else{ #si l'animal existant n'a pas de date de naissance, on l'update
				$dbh->do("UPDATE Animal SET date_naissance=(E'$date') WHERE id_animal=$id");
				$id_anim=$id;
			}
		}
	}
	elsif($size>1){ #s'il existe plusieurs animaux avec ce code
		foreach my $i(@tab){
			my @temp=split(',',$i);
			my $id=$temp[0];
			$id=~s/\(//; #id trouvé
			my $nai=$temp[1];
			$nai=~s/\)//; #date de naissance trouvée
			#~ print "$id; $nai,\n";
			if($nai ne ' '){ #si l'animal existant a une date de naissance
				if($nai eq $date){ #on vérifie que les dates coïncident
					$id_anim=$id;
					last;
				}
			}
			elsif($nai eq ' '){ #si l'animal trouvé n'a pas de date de naissance, on lui en met une
				$dbh->do("UPDATE Animal SET date_naissance=(E'$date') WHERE id_animal=$id");
				$id_anim=$id;
				last;
			}
		}
		if(not $id_anim){ #si aucune date ne correspond, on créé un nouvel animal
			$dbh->do("INSERT INTO Animal(code_animal,date_naissance) VALUES ('$code','$date')");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code') and date_naissance=(E'$date')");
		}
	}
		
	foreach my $key (sort keys %{$result{$code}}) {
		if ($key ne 'naissance'){
			my %id_test2;
			my %result2;
			my %id_res2;
			my %id_type_test2;
			my $datet=$key; #date du test
			
			my $cpt=1;
			foreach (sort keys %{$result{$code}{$key}}){
				$result2{$cpt}=$result{$code}{$key}{$_}[0]; #temps pour chaque trial
				$cpt++;
				$result2{$cpt}=$result{$code}{$key}{$_}[1]; #nb erreurs pour chaque trial
				$cpt++;
			}

			#modification de id_type_test en fonction du nombre de valeurs que contient result2
			foreach my $key(sort keys (%result2)){
				if($result2{$key} ne ''){
					$id_type_test2{$key}=$id_type_test[$key-1];
				}
			}
			
			# ************************************* Création test ************************************* #
			
			### verification que le test n'existe pas deja ###
			my @id_type_test3;
			my $prep_test=$dbh->prepare("SELECT Test.id_type_test FROM Test, Type_test WHERE Type_test.description='barnes bc12' AND Test.id_type_test=Type_test.id_type_test AND experimentateur='$exp' AND date_test=(E'$datet')");
			my $prep_test2=$dbh->prepare("SELECT id_test FROM Test WHERE experimentateur='$exp' AND Test.id_type_test=? AND date_test=(E'$datet')");
			$prep_test->execute();
			while (my @val=$prep_test->fetchrow_array()){
				push(@id_type_test3,$val[0]);
			}
			$prep_test->finish();
					
			### ajout du test si il n'existe pas ###
			if(@id_type_test3){
				my @temp;
				foreach my $z(sort keys %id_type_test2){
					push(@temp,$id_type_test2{$z});
				}
				if($#id_type_test3<$#temp){
					my $union=List::Compare->new(\@temp, \@id_type_test3);
					my @id_type_test4=$union->get_symmetric_difference;
					foreach my $l(@id_type_test4){
						$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$datet', '$exp', $l)");
					}
				}
			}
			elsif(not @id_type_test3){
				foreach my $l(sort keys %id_type_test2){
					$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$datet', '$exp', $id_type_test2{$l})");
				}
			}
			
			#récupération de l'id_test = hash (id_type_test=>id_test)
			foreach my $k(sort keys %id_type_test2){
				$prep_test2->execute($id_type_test2{$k});
				while(my @val=$prep_test2->fetchrow_array()){
					$id_test2{$k}=$val[0];
				}
				$prep_test2->finish();
			}
			
					
			# ************************************* Création resultat_test ************************************* #
				
			### vérification que resultat_test n'existe pas ###
			my %id_res3;
			my $prep_rtest=$dbh->prepare("SELECT id_res from resultat_test,animal WHERE resultat_test.id_animal= animal.id_animal AND id_test=? and animal.id_animal=$id_anim");
			foreach my $i(sort keys (%id_test2)){
				$prep_rtest->execute($id_test2{$i});
				while (my @val=$prep_rtest->fetchrow_array()){
					$id_res3{$i}=$val[0];
				}
				$prep_rtest->finish();
			}
			### ajout de resultat_test si il n'existe pas ###
			if(%id_res3){
				my @keys_res=keys %id_res3;
				my @keys_test=keys %id_test2;
				if($#keys_res<$#keys_test){
					my $union=List::Compare->new(\@keys_test, \@keys_res);
					my @comp=$union->get_symmetric_difference; #différence au niveau des clés
					foreach my $l(@comp){
						$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2{$l}, $id_anim)");
					}
				}
			}
			elsif(not %id_res3){
				foreach my $i(sort keys (%id_test2)){
					$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2{$i}, $id_anim)");
				}
			}
			
			#récupération de l'id_res = hash (id_type_test=>id_res)
			foreach my $cle(keys (%id_test2)){
				$prep_rtest->execute($id_test2{$cle});
				while(my @val=$prep_rtest->fetchrow_array()){
					$id_res2{$cle}=$val[0];
				}
				$prep_rtest->finish();
			}
			
			# ************************************* Création resultat_test_simple ************************************* #
			
			my $prep_resultat=$dbh->prepare("SELECT id_res FROM resultat_test_simple WHERE mesure=? AND id_res=?");

			foreach my $cle(keys (%result2)){
				if($result2{$cle} ne ''){
					foreach my $cle2(keys (%id_res2)){
						if($cle eq $cle2){
							$prep_resultat->execute($result2{$cle},$id_res2{$cle2});
							my @val=$prep_resultat->fetchrow_array();
							my $size3=@val;
							$prep_test->finish();
							if($size3 eq 0){ #si l'animal est resté au centre
								$dbh->do("INSERT INTO Resultat_test_simple(id_res, mesure) VALUES($id_res2{$cle2}, $result2{$cle})");					
							}
						}
					}
				}
			}	
		}
	}
}

$dbh->disconnect();
